﻿using BookManege.entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BookManege.service
{
    internal class StillApplication
    {
        private MySqlConnection mySqlConnection;
        public StillApplication()
        {
            this.mySqlConnection = DatabaseUtil.GetMysqlConnection();
        }
        public DataTable GetUserStillApplication()
        {
            return DatabaseUtil.GetDataTable("select s.id as 申请号,borrow_id as 借阅单号,book_name as 书名,submit_date as 提交时间,s.state as 处理情况," +
                "processing_result as 处理结果,processing_date as 处理时间,user.id as 处理人 from still_application s " +
                "left join borrow b on s.borrow_id=b.id left join user on user.id=processing_by left join book on b.book_id=book.id  where b.user_id="+Program.user.Id);
        }
        public DataTable GetAllStillApplication()
        {
            return DatabaseUtil.GetDataTable("select s.id as 申请号,borrow_id as 借阅单号,book_name as 书名,user_name as 申请人,submit_date as 提交时间,s.state as 处理情况," +
               "processing_result as 处理结果,processing_date as 处理时间 from still_application s " +
               "left join borrow b on s.borrow_id=b.id left join user on user.id=b.user_id left join book on b.book_id=book.id");
        }
        public StillDetails GetDetails(long applicationId)
        {
            string sql = "select book_id,borrow_id,book_name,borrow.number,user.user_name,submit_date,still_application.state from still_application " +
                "left join borrow on still_application.borrow_id=borrow.id left join book on borrow.book_id=book.id left join user on borrow.user_id=user.id where still_application.id=" + applicationId;
            MySqlCommand cmd = new MySqlCommand(sql,mySqlConnection);
            MySqlDataReader mySqlDataReader= cmd.ExecuteReader();
            if(mySqlDataReader.Read())
            {
                long bookId = mySqlDataReader.GetInt64(0);
                long borrowId = mySqlDataReader.GetInt64(1);
                string bookName = mySqlDataReader.GetString(2);
                int number = mySqlDataReader.GetInt32(3);
                string userName = mySqlDataReader.GetString(4);
                DateTime submiteDate = mySqlDataReader.GetDateTime(5);
                string state = mySqlDataReader.GetString(6);
                StillDetails stillDetails = new StillDetails
                {
                    BookId = bookId,
                    BorrowId = borrowId,
                    BookName = bookName,
                    Number = number,
                    UserName = userName,
                    SubmiteDate = submiteDate,
                    State = state
                };
                mySqlDataReader.Close();
                return stillDetails;
            }
            mySqlDataReader.Close();
            return null;
        }
        public DataTable Serch(string state,string process)
        {
            if (state != null)
            {
                return DatabaseUtil.GetDataTable("select s.id as 申请号,borrow_id as 借阅单号,book_name as 书名,user_name as 申请人,submit_date as 提交时间,s.state as 处理情况," +
               "processing_result as 处理结果,processing_date as 处理时间 from still_application s " +
               "left join borrow b on s.borrow_id=b.id left join user on user.id=b.user_id left join book on b.book_id=book.id where s.state='" + state+"'");
            }
            else
            {
                return DatabaseUtil.GetDataTable("select s.id as 申请号,borrow_id as 借阅单号,book_name as 书名,user_name as 申请人,submit_date as 提交时间,s.state as 处理情况," +
              "processing_result as 处理结果,processing_date as 处理时间 from still_application s " +
              "left join borrow b on s.borrow_id=b.id left join user on user.id=b.user_id left join book on b.book_id=book.id where s.processing_result='" + process + "'");
            }
        }
        public int Pass(long applicationId,long bookId,long borrowId,int number)
        {
            string sql1 = "update still_application set state ='1',processing_result='1',processing_date = CURDATE(),processing_by=" + Program.user.Id +" where id ="+applicationId;
            string sql2 = "update borrow set is_still = 1,still_date = CURDATE() where id=" + borrowId;
            string sql3 = "update book set number=number+" + number + " where id=" + bookId;
            MySqlCommand mySqlCommand = new (sql1,mySqlConnection);
            MySqlCommand mySqlCommand1 = new (sql2,mySqlConnection);
            MySqlCommand mySqlCommand2 = new  (sql3,mySqlConnection);
            if (mySqlCommand.ExecuteNonQuery() > 0)
            {
                if (mySqlCommand1.ExecuteNonQuery() > 0)
                {
                    return mySqlCommand2.ExecuteNonQuery();
                }
                return 0;
            }
            return 0;
        }
        public int Refuse(long applicationId)
        {
            string sql = "update still_application set state ='1',processing_result='0',processing_date = CURDATE(),processing_by=" + Program.user.Id + " where id =" + applicationId;
            MySqlCommand mySqlCommand = new(sql, mySqlConnection);
            return mySqlCommand.ExecuteNonQuery();
        }
    }
}
